 package com.example.sixtrainingdemo.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.Log;

import com.example.sixtrainingdemo.R;
import com.example.sixtrainingdemo.entity.Goods;
import com.example.sixtrainingdemo.entity.Photo;
import com.example.sixtrainingdemo.entity.Shop;
import com.example.sixtrainingdemo.entity.User;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

 public class DBHelper extends SQLiteOpenHelper {

     private static final String TAG = "DBHelper";
     private static final String DATABASE_NAME = "znb.db";// 数据库名
     private static final int DATABASE_VERSION = 1;// 数据库版本号
     public static final String TABLE_NAME_GOODS = "goods"; //商品表
     private static final String TABLE_NAME_USER = "user"; //用户表

     private static final String TABLE_NAME_ROUND = "round";

     private static final String TABLE_NAME_ORDER = "order2"; // 订单表 order是关键字
     private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
     private static final String TABLE_NAME_PHOTO = "photo"; //照片表
     private static final String TABLE_NAME_ROUNDPHOTO = "roundphoto"; //农友圈照片表
     private static final String TABLE_NAME_TALK = "talk"; //聊天表
     private static final String TABLE_NAME_SHOP = "shop"; //购物表
//你好

     private static DBHelper mHelper = null;


     private Context mContext;

     // DBHelper 的构造函数，接收一个 Context 参数





//你好

     public DBHelper(Context context) {
         super(context, DATABASE_NAME, null, DATABASE_VERSION);
         this.mContext=context;
     }


     //你好
     public static DBHelper getInstance(Context context) {
         if (mHelper == null) {
             mHelper = new DBHelper(context);
         }
         return mHelper;
     }


//你好

     //数据库创建时被调用
     @Override
     public void onCreate(SQLiteDatabase db) {
         initTable(db);
         initData(db);
     }

     //数据库升级
     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         if (oldVersion < 2) { // 数据库 1 -> 2 升级
             // 创建设置表
             db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (keycolumn text primary key,valuecolumn text)");
         }
     }

     /**
      * 初始化数据表
      *
      * @param db 数据库
      */
     private void initTable(SQLiteDatabase db) {
         // 创建商品表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_GOODS + " (" +
                 "_id integer primary key autoincrement," +
                 "goods_name text," +
                 "goods_price double," +
                 "goods_unit text," +
                 "goods_browse integer," +
                 "goods_message text)");
         // 创建用户表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER + " (" +
                 "_id integer primary key autoincrement," +
                 "user_name text," +
                 "user_password text)");
         // 创建订单表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ORDER + " (" +
                 "_id integer primary key autoincrement," +
                 "order_name text," +
                 "order_number  bigint," +
                 "user_id  integer," +
                 "FOREIGN KEY (user_id) REFERENCES user(_id))");
         // 创建图片表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PHOTO + " (" +
                 "_id integer primary key autoincrement," +
                 "goods_id  integer," +
                 "image BLOB," +
                 "FOREIGN KEY (goods_id) REFERENCES goods(_id))");
         // 创建农友圈表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ROUND + " (" +
                 "_id integer primary key autoincrement," +
                 "round_message  text)");
         //创建农友圈的图片表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ROUNDPHOTO + " (" +
                 "_id integer primary key autoincrement," +
                 "round_id  integer," +
                 "image BLOB," +
                 "FOREIGN KEY (round_id) REFERENCES round(_id))");
         //创建聊天表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_TALK + " (" +
                 "_id integer primary key autoincrement," +
                 "talk_message  text)"); 
         // 创建购物车表
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SHOP + " (" +
                 "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                 "shop_name TEXT," +
                 "shop_price REAL," +
                 "shop_unit TEXT," +
                 "shop_browse INTEGER," +
                 "shop_message TEXT)");
     }

     /**
      * 初始化数据库内部模拟数据(仅在第一次安装时执行)
      *
      * @param db 数据库
      */
     private void initData(SQLiteDatabase db) {
         db.execSQL("insert into " + TABLE_NAME_GOODS +
                 " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                 "values" +
                 "('四川峨眉山黄瓜', 1.23, '斤', '123', '来自四川雪山之巅的黄瓜。')");
         db.execSQL("insert into " + TABLE_NAME_GOODS +
                 " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                 "values" +
                 "('东北大米', 3.33, '斤', '444', '来自东北的大米，煮饭特别香。')");
         db.execSQL("insert into " + TABLE_NAME_GOODS +
                 " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                 "values" +
                 "('海南香蕉', 9.99, '斤', '213', '海南香蕉，特别好吃')");
         db.execSQL("insert into " + TABLE_NAME_GOODS +
                 " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                 "values" +
                 "('新疆又香又甜哈密瓜', 5.99, '斤', '333', '又大又甜，特别好吃')");
         db.execSQL("insert into " + TABLE_NAME_GOODS +
                 " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                 "values" +
                 "('江苏黄瓜', 1.12, '斤', '223', '来自江苏的黄瓜。')");

         db.execSQL("insert into " + TABLE_NAME_USER +
                 " (user_name , user_password) " +
                 "values" +
                 "('1', '1')");
         db.execSQL("insert into " + TABLE_NAME_USER +
                 " (user_name , user_password) " +
                 "values" +
                 "('2', '2')");
         db.execSQL("insert into " + TABLE_NAME_USER +
                 " (user_name , user_password) " +
                 "values" +
                 "('3', '3')");

         db.execSQL("insert into " + TABLE_NAME_ROUND +
                 " (round_message ) " +
                 "values" +
                 "('你好')");
         db.execSQL("insert into " + TABLE_NAME_ROUND +
                 " (round_message) " +
                 "values" +
                 "('我的')");
         db.execSQL("insert into " + TABLE_NAME_ROUND +
                 " (round_message) " +
                 "values" +
                 "('世界')");


//         Bitmap bitmap = BitmapFactory.decodeResource(this.mContext.getResources(), R.drawable.h3);
//         ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
//         bitmap.compress(Bitmap.CompressFormat.PNG, 100, outputStream);
//         byte[] imageBytes = outputStream.toByteArray();
//         // 关闭流
//         try {
//             outputStream.close();
//         } catch (IOException e) {
//             e.printStackTrace();
//         }
//         // 编写 SQL 插入语句
//         String insertPhotoSql = "INSERT INTO " + TABLE_NAME_PHOTO + " (goods_id, image) VALUES (?, ?)";
//         ContentValues contentValues = new ContentValues();
//         contentValues.put("goods_id", 1); // 假设图片属于 goods_id 为 1 的商品
//         contentValues.put("image", imageBytes);
//
//         // 执行插入操作
//         long result = db.insert(TABLE_NAME_PHOTO, null, contentValues);
//         Log.d(TAG, "Inserted photo with result: " +




         insertPhoto(db, R.drawable.h3, 1);
         insertPhoto(db, R.drawable.h4, 2);
         insertPhoto(db, R.drawable.h6, 3);
         insertPhoto(db, R.drawable.h5, 4);
         insertPhoto(db, R.drawable.h7, 5);


         insertRoundPhoto(db, R.drawable.r1, 1);
         insertRoundPhoto(db, R.drawable.r3, 2);
         insertRoundPhoto(db, R.drawable.r2, 3);


     }




//你好

     public int insertUser(User user) {
         //获取可写数据库
         SQLiteDatabase db = getWritableDatabase();

         //创建ContentValues以Key-Value的形式封装数据
         ContentValues values = new ContentValues();
         values.put("user_name", user.getUserName());
         values.put("user_password", user.getUserPassword());

         //执行insert方法，插入数据
         long newRowId = db.insert(TABLE_NAME_USER, null, values);
         Log.d(TAG, "insertUser newRowId = " + newRowId);
         return (int) newRowId;
     }


     public int insertShop(Shop shop) {
         //获取可写数据库
         SQLiteDatabase db = getWritableDatabase();

         //创建ContentValues以Key-Value的形式封装数据
         ContentValues values = new ContentValues();
         values.put("_id", shop.getId());
         values.put("shop_name", shop.getName());
         values.put("shop_price", shop.getPrice());
         values.put("shop_unit", shop.getUnit());
         values.put("shop_message", shop.getMessage());

         //执行insert方法，插入数据
         long newRowId = db.insert(TABLE_NAME_SHOP, null, values);
         Log.d(TAG, "insertShop newRowId = " + newRowId);
         return (int) newRowId;
     }


     @SuppressLint("Range")
     public User login(String userName, String userPassword) {
         SQLiteDatabase db = getReadableDatabase();

         Cursor cursor = db.query(
                 TABLE_NAME_USER,                                                         // 表名
                 null,                                                                      // 要查询的列数据
                 "(user_name = ? ) and user_password = ?", // 条件语句
                 new String[]{userName, userPassword},                     // 条件语句的值
                 null,                                                                      // group by
                 null,                                                                      // having
                 null                                                                       // 排序语句
         );
         User user = null;
         if (cursor != null && cursor.getCount() > 0) {
             if (cursor.moveToFirst()) {
                 user = new User();
                 user.setUserName(cursor.getString(cursor.getColumnIndex("user_name")));
                 user.setUserPassword(cursor.getString(cursor.getColumnIndex("user_password")));
                 Log.d(TAG, "login = " + user.toString());
             }
         }

         return user;

     }

     @SuppressLint("Range")
     public List<User> selectAllUser() {
         List<User> list = new ArrayList<>();
         SQLiteDatabase db = getReadableDatabase();

         Cursor cursor = db.query(
                 TABLE_NAME_USER,     // 表名
                 null,                   // 要查询的列数据
                 null,                   // 条件语句
                 null,                   // 条件语句的值
                 null,                   // group by
                 null,                   // having
                 null                    // 排序语句
         );
         if (cursor != null && cursor.getCount() > 0) {
             while (cursor.moveToNext()) {
                 User user = new User();
                 user.setUserName(cursor.getString(cursor.getColumnIndex("user_name")));
                 user.setUserPassword(cursor.getString(cursor.getColumnIndex("user_password")));
                 list.add(user);
             }
         }
         Log.d(TAG, "selectAllUser = " + list.size());
         return list;
     }


     @SuppressLint("Range")
     public Goods selectAllGoods(int goodsId) {
         SQLiteDatabase db = getReadableDatabase();

         Cursor cursor = db.query(
                 TABLE_NAME_GOODS,     // 表名
                 null,                   // 要查询的列数据
                 "_id = ?",                   // 条件语句
                 new String[]{String.valueOf(goodsId)},                   // 条件语句的值
                 null,                   // group by
                 null,                   // having
                 null                    // 排序语句
         );
         Goods goods = new Goods();
         if (cursor != null && cursor.moveToFirst()) {

             goods.setId(cursor.getInt(cursor.getColumnIndex("_id")));
             goods.setName(cursor.getString(cursor.getColumnIndex("goods_name")));
             goods.setPrice(cursor.getDouble(cursor.getColumnIndex("goods_price")));
             goods.setUnit(cursor.getString(cursor.getColumnIndex("goods_unit")));
//             goods.setBrowseCount(cursor.getString(cursor.getColumnIndex("goods_browse")));
             goods.setMessage(cursor.getString(cursor.getColumnIndex("goods_message")));
         }
         Log.d(TAG, "selectAllGood = " + goods);
         return goods;
     }


     @SuppressLint("Range")
     public List<Photo> selectAllPhoto() {
         List<Photo> list = new ArrayList<>();
         SQLiteDatabase db = getReadableDatabase();

         Cursor cursor = db.query(
                 TABLE_NAME_GOODS,     // 表名
                 null,                   // 要查询的列数据
                 null,                   // 条件语句
                 null,                   // 条件语句的值
                 null,                   // group by
                 null,                   // having
                 null                    // 排序语句
         );
         if (cursor != null && cursor.getCount() > 0) {
             while (cursor.moveToNext()) {
                 Photo photo = new Photo();
                 photo.setId(cursor.getInt(cursor.getColumnIndex("_id")));
//             photo.setId(cursor.getString(cursor.getColumnIndex("goods_id")));
//             photo.setBitmap(cursor.getString(cursor.getColumnIndex("image")));

                 list.add(photo);
             }
         }
         Log.d(TAG, "selectAllStudent = " + list.size());
         return list;
     }

     public List<Goods> selectAllGoodsById(int goodsId) {
         List<Goods> goodsList = new ArrayList<>();
         SQLiteDatabase db = this.getReadableDatabase();
         String query = "SELECT * FROM " + TABLE_NAME_GOODS + " WHERE _id = ?";
         Cursor cursor = db.rawQuery(query, null);
         if (cursor.moveToFirst()) {
             do {
                 Goods goods = new Goods();
                 // 填充Goods对象的字段...
                 goodsList.add(goods);
             } while (cursor.moveToNext());
         }
         cursor.close();
         db.close();
         return goodsList;
     }



     @SuppressLint("Range")
     public List<Shop> selectAllShop() {
         List<Shop> list = new ArrayList<>();
         SQLiteDatabase db = getReadableDatabase();

         Cursor cursor = db.query(
                 TABLE_NAME_SHOP,     // 表名
                 null,                   // 要查询的列数据
                 null,                   // 条件语句
                 null,                   // 条件语句的值
                 null,                   // group by
                 null,                   // having
                 null                    // 排序语句
         );
         if (cursor != null && cursor.getCount() > 0) {
             while (cursor.moveToNext()) {
                 Shop shop = new Shop();
                 shop.setId(cursor.getInt(cursor.getColumnIndex("_id")));
                 shop.setName(cursor.getString(cursor.getColumnIndex("shop_name")));
                 shop.setPrice(cursor.getDouble(cursor.getColumnIndex("shop_price")));
                 shop.setUnit(cursor.getString(cursor.getColumnIndex("shop_unit")));
                 shop.setMessage(cursor.getString(cursor.getColumnIndex("shop_message")));
                 list.add(shop);
             }
         }
         Log.d(TAG, "selectAllShop = " + list.size());
         return list;
     }


//     @SuppressLint("Range")
//     public List<Photo> selectAllPhotosByGoodsId(int goodsId) {
//         List<Photo> list = new ArrayList<>();
//         SQLiteDatabase db = getReadableDatabase();
//         Cursor cursor = db.query(
//                 TABLE_NAME_PHOTO,
//                 null,
//                 "goods_id = ?",
//                 new String[]{String.valueOf(goodsId)},
//                 null,
//                 null,
//                 null
//         );
//
//         if (cursor != null && cursor.moveToFirst()) {
//             do {
//                 Photo photo = new Photo();
//                 photo.setId(cursor.getInt(cursor.getColumnIndex("_id")));
//                 photo.setData(cursor.getBlob(cursor.getColumnIndex("image"))); // 假设图片以 BLOB 形式存储
//                 list.add(photo);
//             } while (cursor.moveToNext());
//         }
//         return list;
//     }


     @SuppressLint("Range")
     public List<Photo> selectAllPhotosByGoodsId(int goodsId) {
         List<Photo> list = new ArrayList<>();
         SQLiteDatabase db = getReadableDatabase();
         try (Cursor cursor = db.query(
                 TABLE_NAME_PHOTO,
                 new String[]{"_id", "image"}, // 只查询需要的列
                 "goods_id = ?",
                 new String[]{String.valueOf(goodsId)},
                 null,
                 null,
                 null)) {
             if (cursor != null) {
                 while (cursor.moveToNext()) {
                     Photo photo = new Photo();
                     photo.setId(cursor.getInt(cursor.getColumnIndex("_id")));
                     // 检查图片数据是否为空
                     byte[] imageBytes = cursor.getBlob(cursor.getColumnIndex("image"));
                     if (imageBytes != null && imageBytes.length > 0) {
                         // 这里可以选择将图片数据转换为 Bitmap 或其他格式
                         // 但请注意内存使用
                         photo.setData(imageBytes);
                     }
                     list.add(photo);
                 }
             }
         }
         return list;
     }







     private void insertPhoto(SQLiteDatabase db, int imageResId, int goodsId) {
         // 原始图片
         Bitmap bitmap = BitmapFactory.decodeResource(mContext.getResources(), imageResId);

         // 压缩图片的尺寸比例
         float scaleFactor = 0.1f; // 10% 原始尺寸
         int newWidth = (int) (bitmap.getWidth() * scaleFactor);
         int newHeight = (int) (bitmap.getHeight() * scaleFactor);

         // 创建缩放后的图片
         Bitmap resized = Bitmap.createScaledBitmap(bitmap, newWidth, newHeight, true);

         // 使用 ByteArrayOutputStream 来保存压缩后的图片数据
         ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
         resized.compress(Bitmap.CompressFormat.JPEG, 100, outputStream);

         // 将压缩后的图片数据转换为字节数组
         byte[] imageBytes = outputStream.toByteArray();

         try {
             // 关闭 ByteArrayOutputStream，释放资源
             outputStream.close();
         } catch (IOException e) {
             e.printStackTrace();
         }

         // 编写 SQL 插入语句
         ContentValues contentValues = new ContentValues();
         contentValues.put("goods_id", goodsId);
         contentValues.put("image", imageBytes);

         // 执行插入操作
         long result = db.insert(TABLE_NAME_PHOTO, null, contentValues);
         if (result != -1) {
             Log.d(TAG, "Inserted photo with result: " + result);
         } else {
             Log.e(TAG, "Failed to insert photo");
         }
     }



     private void insertRoundPhoto(SQLiteDatabase db, int imageResId, int roundId) {
         Bitmap bitmap = BitmapFactory.decodeResource(mContext.getResources(), imageResId);
         ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
         bitmap.compress(Bitmap.CompressFormat.PNG, 100, outputStream);
         byte[] imageBytes = outputStream.toByteArray();

         try {
             outputStream.close();
         } catch (IOException e) {
             e.printStackTrace();
         }

         // 编写 SQL 插入语句
         ContentValues contentValues = new ContentValues();
         contentValues.put("round_id", roundId);
         contentValues.put("image", imageBytes);

         // 执行插入操作
         long result = db.insert(TABLE_NAME_ROUNDPHOTO, null, contentValues);
         if (result != -1) {
             Log.d(TAG, "Inserted roundphoto with result: " + result);
         } else {
             Log.e(TAG, "Failed to insert roundphoto");
         }
     }


     public int deleteShop(int shopId) {
         SQLiteDatabase db = getWritableDatabase();
         // 编写 SQL 删除语句
         int rowsDeleted = db.delete(
                 TABLE_NAME_SHOP,  // 表名
                 "_id = ?",        // 条件语句
                 new String[]{String.valueOf(shopId)}  // 条件语句的值
         );
         return rowsDeleted;
     }















   //你好

}
